package com.scalar.chiptrack.adaptor;

import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.dao.TestAdaptorDAO;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.finance.openpo.forms.PurchaseOrderForm;
import com.scalar.chiptrack.finance.openpo.PurchaseOrder;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.ScalarException;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: munugoti
 * Date: Dec 12, 2008
 * Time: 7:19:58 AM
 * To change this template use File | Settings | File Templates.
 */
public class DataPortingParser {

    public static void main ( String[] agrs ) throws Exception
    {
        DataPortingParser portingParser = new DataPortingParser();
        portingParser.execute();
    }
    public void execute() throws Exception
    {
        try
        {
            File portingFile =  getFileForPorting( System.getProperty("portingFile") );

            Parser parser = ParserFactory.getXLSParser( portingFile, 0 );

           //updateDieBakBinInfo( parser );

           //updateSpecAndMarkingInfo( parser );

            //updateCostAndYieldInfo ( parser);

            //updatePoInfo( parser );
            
            //updateCycleTimeInfo( parser );

            //updateFabProcessData( parser );

             //updateFabInvoiceCostData( parser );
            
             //updateCustomerNoData( parser );

             //updateAddressTagName( parser);

            //updateCostAndYieldCycleTimeInfo ( parser);

            ArrayList testBinList = updateTestYieldInfo( parser );

            if ( testBinList != null && testBinList.size() > 0 )
            {
                //to get the ASECL Test Bin Details
                if ( testBinList.isEmpty() == false )
                {
                    TestAdaptorDAO.saveTestBinInfo ( null, testBinList , "UNISEM" );

                }

            }

            //updateDistributorInventory( parser);

			//updateSiTimeMalysiaInventory( parser);
            //updateSiTimeYearEndScrap( parser, "SCRAP");

            //updateAATIProducts( parser);
        }
        catch ( Exception e)
        {
            System.out.println ( "========parser exception=====");
            e.printStackTrace();
        }
    }

    private static void updateAATIProducts( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(6);
        ((XLSParserImpl)parser).setDataStartRowIdx(7);
        try
        {
            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_PRODUCT_AATI (?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            while ( result.next() )
            {
                pstmt.setString( 1, result.getString ( "PART NO" ) );
                pstmt.setString( 2, result.getString ( "LEVEL 1 COMPONENT" ) );
                pstmt.setString( 3, result.getString ( "LEVEL 2 COMPONENT" ) );
                pstmt.setString( 4, result.getString ( "LEVEL 3 COMPONENT" ) );
                pstmt.setString( 5, result.getString ( "LEVEL 4 COMPONENT" ) );

                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }
        catch(Exception e)
        {
            System.out.println("result.getString ( \"PART NO\" ) = " + result.getString("PART NO"));
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    private static void updateDieBakBinInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(2);
        ((XLSParserImpl)parser).setDataStartRowIdx(3);
        try
        {
            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_DIEBANK_BININFO (?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            while ( result.next() )
            {
                pstmt.setString( 1, result.getString ( "PART_NUMBER" ) );
                pstmt.setString( 2, result.getString ( "LOT_NUMBER" ) );
                pstmt.setString( 3, result.getString ( "LOCATION" ) );
                for (int i = 1 ; i <= 25 ; i++ )
                {
                    if ( result.getInt( i+"" ) > 0 )
                    {
                        pstmt.setInt( 4, i );
                        pstmt.setInt( 5, result.getInt ( i+"" ) );

                        pstmt.addBatch();
                    }
                }
               //System.out.println ( result.getString ( "PART_NUMBER" ) + ":" + result.getString( "FAB_LOT_NUMBER" ) );
            }
            pstmt.executeBatch();
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }


    private static void updateSpecAndMarkingInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(0);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_MARK_SPEC_INFO (?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "SPECIFICATION FILE NAME" ) != null && result.getString ( "SPECIFICATION FILE NAME" ).trim().length() > 0 )
                {
                    pstmt.setString( 1, result.getString ( "VENDOR NAME" ) );
                    pstmt.setString( 2, result.getString ( "PART NUMBER" ) );
                    pstmt.setString( 3, result.getString ( "SPECIFICATION FILE NAME" ) );
                    pstmt.setString( 4, result.getString ( "MARKING FIRST LINE" ) );
                    pstmt.setString( 5, result.getString ( "MARKING SECOND LINE" ) );
                    pstmt.setString( 6, result.getString ( "MARKING THIRD LINE" ) );
                    pstmt.setString( 7, result.getString ( "MARKING FOURTH LINE" ) );
                    pstmt.setString( 8, result.getString ( "MARKING FIFTH LINE" ) );
                    pstmt.setString( 9, result.getString ( "MARKING SIXTH LINE" ) );

                    pstmt.addBatch();
                    //System.out.println ( result.getString ( "VENDOR: " ) + ":" + result.getString ( "Manufacturing Part Number" ) + ":" + result.getString ( "Bonding Diagram:" ) );
                }
                else
                {
                    System.out.println ( "IN ELSE " + result.getString ( "SPECIFICATION FILE NAME" ) + ":" + result.getString ( "VENDOR NAME" ) );
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    private static void updateCostAndYieldInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(0);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_view_cost_by_Base_Product (?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Wafer PN" ) != null && result.getString ( "Wafer PN" ).trim().length() > 0 )
                {
                    if ("Level".equalsIgnoreCase(result.getString ( "Wafer PN" ).trim() ) )
                    {
                        break;
                    }
                    pstmt.setString( 1, result.getString ( "Wafer PN" ).trim() );
                    pstmt.setString( 2, result.getString ( "Supplier" ).trim() );
                    pstmt.setDouble( 3, result.getDouble ( "Fab" ) );
                    pstmt.setDouble( 4, result.getDouble ( "Sort" ) );
                    pstmt.setDouble( 5, Math.round(result.getDouble("Sort Yield") * 100 ) );
                    pstmt.setDouble( 6, result.getDouble("Assy") );
                    pstmt.setDouble( 7, Math.round(result.getDouble("Assy Yield") * 100 ) );
                    pstmt.setDouble( 8, result.getDouble("Test") );
                    pstmt.setDouble( 9, Math.round(result.getDouble("Test Yield") * 100 ) );
                    pstmt.setDouble( 10, result.getDouble("BE TL") );

                    pstmt.addBatch();

                    //System.out.println ( "DOULBLE VALUE : " + result.getDouble ( "Q109" ) + ":   " + result.getDouble ( "Sort" )  + ":   " + result.getDouble("Assy") + " :    " + result.getDouble("Test") + " :    " +  result.getDouble("BE (Dry Pack)") );
                    //System.out.println ( "DOULBLE VALUE YIELD"  + Math.round(result.getDouble("Sort Yield") * 100 ) + " :  " + Math.round(result.getDouble("Assy Yield") * 100 ) + " :  "+Math.round(result.getDouble("Test Yield") * 100 ) );
                }
                else
                {
                    System.out.println ( "IN ELSE " + result.getString ( "Wafer PN" ) + ":" +  result.getString ( "Sort" )  + ":" + result.getString("Sort Yield") );
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    private static void updateCostAndYieldCycleTimeInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(4);
        ((XLSParserImpl)parser).setDataStartRowIdx(5);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_cost_cylce_time_by_FG_Product (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Part Number" ) != null && result.getString ( "Part Number" ).trim().length() > 0
                        && !"tbd".equalsIgnoreCase(result.getString ( 32 ) ) )
                {
                    pstmt.setString( 1, result.getString ( "Part Number" ).trim() );
                    pstmt.setString( 2, null );
                    pstmt.setDouble( 3, (result.getString ( 32 )!= null && result.getString(32).trim().length() > 0 ) ? result.getDouble ( 32 ) : 0.0  );
                    pstmt.setDouble( 4, (result.getString ( 33 )!= null && result.getString(33).trim().length() > 0 ) ? result.getDouble ( 33 ) : 0.0  );
                    pstmt.setDouble( 5, result.getDouble(34) );
                    pstmt.setDouble( 6, result.getDouble(35) );
                    pstmt.setDouble( 7, result.getDouble(38) );  //BE + TTR

                    pstmt.setDouble( 8, Math.round(result.getDouble(45) * 100 ) );
                    pstmt.setDouble( 9, Math.round(result.getDouble(46) * 100 ) );
                    pstmt.setDouble( 10, Math.round(result.getDouble(47) * 100 ) );

					pstmt.setInt( 11, result.getInt ( "Start Queue" ) + result.getInt(50) ); //Fab Cycle Time
					pstmt.setInt( 12, result.getInt(51) );  //Sort Cycle Time
					pstmt.setInt( 13, result.getInt(52) ); //Assm Cycle Time
					pstmt.setInt( 14, result.getInt(53) ); //Test Cycle Time
					pstmt.setInt( 15, result.getInt(54) ); //EOL Cycle Time

                    pstmt.addBatch();

                   //System.out.println ( "DOULBLE VALUE : " + result.getDouble ( 32 ) + ":   " + result.getDouble ( 33 )  + ":   " + result.getDouble ( 34 )  );
                   //System.out.println ( "DOULBLE VALUE YIELD"  + Math.round(result.getDouble(45) * 100 )  + " :  " + Math.round(result.getDouble(46) * 100 )  + " :  "+Math.round(result.getDouble(47) * 100 )  );
                }
                else
                {
                    //System.out.println ( "IN ELSE " + result.getString ( "Wafer PN" ) + ":" +  result.getString ( "Sort" )  + ":" + result.getString("Sort Yield") );
                    System.out.println ( "IN ELSE " );
                }
            }
            pstmt.executeBatch();
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    private static void updateCycleTimeInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(2);
        ((XLSParserImpl)parser).setDataStartRowIdx(3);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_cylce_time_by_Base_Product (?, ?, ?, ?, ?, ?,?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Wafer Part" ) != null && result.getString ( "Wafer Part" ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( "Wafer Part" ).trim() );
                    pstmt.setInt( 2, result.getInt ( "Start Queue" ) + result.getInt("FAB") );
                    pstmt.setInt( 3, result.getInt ( "Sort" ) );
                    pstmt.setInt( 4, result.getInt ( "Assy" ) );
                    pstmt.setInt( 5, result.getInt("FT") );
                    pstmt.setInt( 6, result.getInt("EOL") );
                    pstmt.setString( 7, result.getString(13).replaceAll("%","") );
                    pstmt.setString( 8, result.getString(14).replaceAll("%","") );
                    pstmt.setString( 9, result.getString("Test").replaceAll("%","") );

                    pstmt.addBatch();

                    //System.out.println ( "DOULBLE VALUE : " + result.getInt ( "Start Queue" ) + ":  " + result.getInt ( "FAB" ) + ":   " + result.getInt ( "Sort" )  + ":   " + result.getInt("Assy") + " :    " + result.getInt("FT") + " :    " +  result.getInt("EOL") );

                }
                else
                {
                    System.out.println ( "IN ELSE " + result.getString ( "Wafer Part" ) + ":" +  result.getString ( "Sort" )  );
                }
            }
            pstmt.executeBatch();
            
            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {                        
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    private static void updateFabProcessData( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(1);
        ((XLSParserImpl)parser).setDataStartRowIdx(2);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_Fab_Process_Data (?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Wafer PN" ) != null && result.getString ( "Wafer PN" ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( "Wafer PN" ).trim() );
                    pstmt.setString( 2, result.getString ( "Wafer & Pkg" ) );
                    pstmt.setInt( 3, result.getInt ( "GDW" ) );
                    pstmt.setInt( 4, result.getInt ( "NDW" ) );
                    pstmt.setString( 5, result.getString("Process") );
                    pstmt.setString( 6, result.getString("Fab") );

                    pstmt.addBatch();
                     
                }
                else
                {
                    //System.out.println ( "IN ELSE " + result.getString ( "Wafer Part" ) + ":" +  result.getString ( "Sort" )  );
                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    private static void updateFabInvoiceCostData( Parser parser ) throws Exception
        {
            ParserResult result = null;
            Connection conn         = null;
            PreparedStatement pstmt = null;
            boolean success = false;

            conn = DataSource.getConnection();

            ((XLSParserImpl)parser).setHeaderRowIdx(0);
            ((XLSParserImpl)parser).setDataStartRowIdx(1);
            try
            {
                pstmt = conn.prepareStatement( " { CALL sp_create_update_Fab_Invoice_Cost (?, ?, ? ) }" );
                result = parser.parse();
                //System.out.println("result = " + result.getHeadings() );
                while ( result.next() )
                {
                    if ( result.getString ( "Cust_Prod_Code" ) != null && result.getString ( "Cust_Prod_Code" ).trim().length() > 0  )
                    {   String partNo = null;
                        if ( result.getString ( "Cust_Prod_Code" ).indexOf("-") != -1 )
                        {
                           partNo =   result.getString ( "Cust_Prod_Code" ).substring(0, result.getString ( "Cust_Prod_Code" ).indexOf("-") );
                        }
                        else
                        {
                            partNo = result.getString ( "Cust_Prod_Code" ).trim();
                        }

                        if (result.getDouble ( " Wafer Cost" ) > 0.0 )
                        {
                            pstmt.setString( 1, partNo );
                            pstmt.setString( 2, result.getString ( "Lot_No" ) );
                            pstmt.setDouble( 3, result.getDouble ( " Wafer Cost" ) );

                            pstmt.addBatch();
                        }
                    }
                    else
                    {
                        //System.out.println ( "IN ELSE " + result.getString ( "Wafer Part" ) + ":" +  result.getString ( "Sort" )  );
                    }
                }
                pstmt.executeBatch();

                success = true;
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
            finally
            {

                try
                {
                    if (conn != null)
                    {
                        if( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {System.out.println("Sql exception ");}

                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch (SQLException ignore){}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch (SQLException ignore){}
                }
            }
        }

    private static void updatePoInfo( Parser parser ) throws Exception
    {
        ParserResult result = null;
        boolean success = false;
        PurchaseOrder purchaseOrder = null;

        ((XLSParserImpl)parser).setHeaderRowIdx(2);
        ((XLSParserImpl)parser).setDataStartRowIdx(3);
        try
        {
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Po Number" ) != null && result.getString ( "Po Number" ).trim().length() > 0 )
                {
                    if ("OPEN".equalsIgnoreCase(result.getString ( "PO Line Close Code" ).trim() ) )
                    {
                        purchaseOrder = new PurchaseOrder();

                        purchaseOrder.setPurchaseOrderNumber( result.getString ( "Po Number" ) );
                        purchaseOrder.setPoLineItemNo( result.getString("Line Num"));
                        purchaseOrder.setVendorName( AdaptorConstants.VENDOR_NAME_TSMC );
                        if (  "STANDARD".equalsIgnoreCase(result.getString("Po Type") ) )
                        {
                            purchaseOrder.setPurchaseOrderType(  2  );
                        }
                        System.out.println("result.getDate(\"Po Date\") = " + result.getDate("Po Date"));
                        System.out.println("result.getString(\"Item Name\") = " + result.getString("Item Name"));
                        System.out.println("result.getString(\"Quantity Ordered\") = " + result.getString("Quantity Ordered"));
                    }
                    //System.out.println ( "DOULBLE VALUE : " + result.getDouble ( "Q109" ) + ":   " + result.getDouble ( "Sort" )  + ":   " + result.getDouble("Assy") + " :    " + result.getDouble("Test") + " :    " +  result.getDouble("BE (Dry Pack)") );
                    //System.out.println ( "DOULBLE VALUE YIELD"  + Math.round(result.getDouble("Sort Yield") * 100 ) + " :  " + Math.round(result.getDouble("Assy Yield") * 100 ) + " :  "+Math.round(result.getDouble("Test Yield") * 100 ) );
                }
                else
                {
                    System.out.println ( "IN ELSE " + result.getString ( "Wafer PN" ) + ":" +  result.getString ( "Sort" )  + ":" + result.getString("Sort Yield") );
                }
            }

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

    }
    private static void updateCustomerNoData( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(-1);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {

            pstmt = conn.prepareStatement( " { CALL sp_create_update_CustomerNo_Data (?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( 0 ) != null && result.getString ( 0 ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( 0 ).trim() );
                    pstmt.setString( 2, result.getString ( 1).trim() );
                    System.out.println("Customer Name " + result.getString ( 1 ).trim() + "   " +result.getString ( 0 ).trim() );
                    pstmt.addBatch();

                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
     private static void updateAddressTagName( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(-1);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL sp_create_update_Customer_Address_Tag ( ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( 0 ) != null && result.getString ( 0 ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( 0 ).trim() );
                    pstmt.setString( 2, result.getString ( 3 ).trim() );
                    pstmt.setString( 3, result.getString ( 4 ).trim() );
                    pstmt.setString( 4, result.getString ( 6 ).trim() );
                    pstmt.setString( 5, result.getString ( 7 ).trim() );
                    pstmt.setString( 6, result.getString ( 8 ).trim() );
                    pstmt.setString( 7, result.getString ( 9 ).trim() );
                    pstmt.setString( 8, result.getString ( 1 ).trim() );

                    pstmt.addBatch();

                }
                else
                {
                    //System.out.println ( "IN ELSE " + result.getString ( "Wafer Part" ) + ":" +  result.getString ( "Sort" )  );
                }
            }
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    private ArrayList updateTestYieldInfo( Parser parser ) throws Exception
    {

        ParserResult result     = null;

        String lotNumber        = null;
        String partNumber       = null;
        String fablotNumber     = null;
        ArrayList ftYieldList  = new ArrayList();
        String line                     = null;

        try
        {

            ((XLSParserImpl)parser).setHeaderRowIdx(1);
            ((XLSParserImpl)parser).setDataStartRowIdx(2);

            result = parser.parse();

            //System.out.println("result.getString() = " + result.getHeadings() );

            while ( result.next() )
            {
                partNumber   = result.getString("DEVICE");
                fablotNumber = result.getString("Wafer Lot No");
                lotNumber    = result.getString("Subcon Lot No");
                if ( lotNumber == null)
                {
                    lotNumber = fablotNumber;
                }

                if ( partNumber != null && partNumber.length() > 0 && lotNumber != null && lotNumber.length() > 0 )
                {
                    TestLotBinReport ftYieldStatus = new TestLotBinReport();

                    partNumber = partNumber.split("#")[0];
                    ftYieldStatus.setDeviceName( partNumber );
                    ftYieldStatus.setLotNumber( lotNumber );
                    ftYieldStatus.setCustLotNo( fablotNumber );
                    //ftYieldStatus.setPoNumber( result.getString("Release No"));
                    ftYieldStatus.setPkgType( result.getString("Package") );
                    ftYieldStatus.setLeadCount( result.getString("L/C") );
                    ftYieldStatus.setStage( result.getString("INSERTION") ); // Insertion
                    ftYieldStatus.setTestProg(result.getString("Program No"));
                    ftYieldStatus.setTesterId( result.getString("Tester ID") );
                    ftYieldStatus.setDateCode( result.getString("Date Code") );
                    ftYieldStatus.setHandler( result.getString("Handler ID") );
                    ftYieldStatus.setLb(result.getString("L/B ID"));
                    ftYieldStatus.setTemp( result.getString("Temp"));
                    ftYieldStatus.setDateIn( StringUtils.formatDate( result.getString("Test In Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setDateOut(StringUtils.formatDate( result.getString("Test Out Date"),"yyyy/MM/dd HH:mm:ss", "MM/dd/yyyy HH:mm:ss" ) );
                    ftYieldStatus.setInQty( result.getInt("In Qty") +"");
                    ftYieldStatus.setPassQty( result.getInt("Out Qty")+"" );
                    //ftYieldStatus.setDamageQty( result.getString("Defect Qty") );

                    //ftYieldStatus.setYield( result.getString("Yield").replaceAll("%",""));
                    ftYieldStatus.setBin1( result.getString("Bin 1") );
                    ftYieldStatus.setBin2(result.getString("Bin 2"));
                    ftYieldStatus.setBin3(result.getString("Bin 3"));
                    ftYieldStatus.setBin4(result.getString("Bin 4"));
                    ftYieldStatus.setBin5(result.getString("Bin 5"));
                    ftYieldStatus.setBin6(result.getString("Bin 6"));
                    ftYieldStatus.setBin7(result.getString("Bin 7"));
                    ftYieldStatus.setBin8(result.getString("Bin 8"));

                    ftYieldStatus.setLossQty( result.getString("Loss QTY") );
                    ftYieldStatus.setVmQty( result.getString("VM QTY") );
                    //ftYieldStatus.setOsQty( result.getString("OS QTY") );

                    ftYieldList.add( ftYieldStatus );

                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the FT Yield Report");
            ex.printStackTrace();
            throw ex;
        }

        return ftYieldList;
    }

    private static void updateDistributorInventory( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(0);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {

            pstmt = conn.prepareStatement( " { CALL sp_create_update_distributor_Inventory (?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Disty" ) != null && result.getString ("Disty").trim().length() > 0 /*&& "KTS".equalsIgnoreCase(result.getString ( "Disty" ).trim() )*/ )
                {
                    pstmt.setString( 1, result.getString ("Disty").trim() );
                    pstmt.setString( 2, result.getString ("Part").trim() );
                    pstmt.setInt( 3, result.getInt ("Qty") );
                    //System.out.println("Customer Name " + result.getString ( "Disty" ) + "   " +result.getString ( 0 ).trim() );
                    pstmt.addBatch();
					System.out.println("#" + result.getString ( "Disty" ) + "#" +  result.getString ("Part").trim() +"#" + result.getInt ("Qty") );
                }
            }
			System.out.println("Customer Name "  );
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
	
	private static void updateSiTimeMalysiaInventory( Parser parser ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(6);
        ((XLSParserImpl)parser).setDataStartRowIdx(7);
        try
        {

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_FG_LOT (?, ?, ?, ?, ?,?,?,?, ?, ?, ?, ?,?,?,?, ?, ?, ?, ?,? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Device" ) != null && result.getString ("Device").trim().length() > 0 && !result.getString ("Device").startsWith("====") 
					 && result.getString ( "Lot#" ) != null && result.getString ("Lot#").trim().length() > 0 )
                {
                    pstmt.setString( 1, result.getString ("Device").trim() );
                    pstmt.setString( 2, result.getString ("Lot#").trim() );
                    pstmt.setInt( 3, result.getInt ("Ship qty") );
					pstmt.setString( 4, null );
					pstmt.setString( 5, "12/02/2010" );
					pstmt.setString( 6, null );
					pstmt.setString( 7, null );
					pstmt.setString( 8, "FG" );
					pstmt.setString( 9, "Finished Goods" );
					pstmt.setString( 10, "SiTime Malaysia" );
					pstmt.setString( 11, "Running" );
					pstmt.setString( 12, null );//stage
					pstmt.setString( 13, result.getString ("Lot#").trim() );
					pstmt.setString( 14, "PROD" );
					pstmt.setString( 15, null );//stage
					pstmt.setString( 16, result.getString("Package" ) );
					pstmt.setString( 17, null );
					pstmt.setString( 18, result.getString("D/C" ) );
					pstmt.setString( 19, result.getString ("Lot#").trim() );	
					pstmt.setString( 20, result.getString("PO# / Release#" ) );


                    //System.out.println("Customer Name " + result.getString ( "Disty" ) + "   " +result.getString ( 0 ).trim() );
                    pstmt.addBatch();
			    }
            }
			
            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

	private static void updateSiTimeYearEndScrap( Parser parser, String type ) throws Exception
    {
        ParserResult result = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        conn = DataSource.getConnection();

        ((XLSParserImpl)parser).setHeaderRowIdx(0);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {

            pstmt = conn.prepareStatement( " { CALL SP_CREATE_UPDATE_YEAR_END_SCRAP_LOT (?, ?, ?, ?, ?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "PART NUMBER" ) != null && result.getString ("PART NUMBER").trim().length() > 0
					 && result.getString ( "Lot Number" ) != null && result.getString ("Lot Number").trim().length() > 0 )
                {
                    pstmt.setString( 1, result.getString ("PART NUMBER").trim() );
                    pstmt.setString( 2, result.getString ("Lot Number").trim() );
                    pstmt.setInt( 3, result.getInt ("Qty In") );
					pstmt.setString( 4, result.getString("Vendor Name") );
					pstmt.setString( 5, result.getString("Process") );
					pstmt.setString( 6, type );

                    //System.out.println("Customer Name " + result.getString ( "Disty" ) + "   " +result.getString ( 0 ).trim() );
                    pstmt.addBatch();
			    }
            }

            pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    private File getFileForPorting ( String customerPortingFile ) throws ScalarException
    {
        if ( customerPortingFile == null || customerPortingFile.trim().length() == 0 )
        {
            System.out.println ( "***ERROR***Porting File Name not specified" );
            throw new ScalarException( "Porting File Name not specified" );
        }
        File portingFile = new File ( customerPortingFile );
        if ( !portingFile.exists()  && !portingFile.isFile() )
        {

            throw new ScalarException( customerPortingFile + " does not exist" );
        }
        else
        {

            System.out.println ( "Porting File:" + customerPortingFile + " Found" );
        }
        return portingFile;
    }
}
